﻿
////////////////////////////////////////////////////////////////////////////////////////////
// 文件名: DictionaryDAL.gen.cs
//
// 模块名称: DictionaryDAL
//
// 作者: LPC
//
// 历史记录（记录修改记录，修改文件后请添加修改记录，注明修改时间、修改人，修改内容）:
// 13-1-29    LPC    创建文件
////////////////////////////////////////////////////////////////////////////////////////////

using System;
using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;
using Common;
using Model;

namespace DAL
{
    /// <summary>
    /// Dictionary表数据访问层
    /// </summary>
    public class DictionaryDAL : DB
    {
        public DictionaryDAL(LoginInfo info)
        {
            base.loginInfo = info;
        }
        /// <summary>
        /// 根据GUID字段获取DictionaryInfo实体对象
        /// </summary>
        /// <param name="guid">GUID</param>
        /// <returns>DictionaryInfo实体对象</returns>
        public DictionaryInfo GetInfoById(string guid)
        {
            var list = new List<DictionaryInfo>();

            //创建DbCommand对象
            HIDB.dbo cmd = base.conn();

            //添加输入输出参数
            IDataParameter[] sqlparams = { 
             new SqlParameter("@GUID",guid)    
            };

            //执行命令返回结果
            try
            {
                DataTable dt = cmd.GetDataTable("select * from Working_db.DictModelType where GUID=@GUID", sqlparams);
                list = CreateDictionaryInfo(dt);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_DictionaryInfo_Insert", "UP_Sys_DictionaryInfo_Insert", 0, 1, ex, "", "");
            }
            if (list != null && list.Count > 0)
            {
                return list[0];
            }
            return null;
        }
        public List<SysConfig> GetSysConfig(string type)
        {
            List<SysConfig> list = new List<SysConfig>();
            HIDB.dbo cmd = base.conn();

            try
            {
                SqlParameter[] para = new SqlParameter[] { 
                new SqlParameter("@type",type)
                };
                DataTable dt = cmd.GetDataTable("select * from Working_db.SysConfig where keytype=@type", para);
                list = dt.MapToList<SysConfig>();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
        public List<UserIFConfig> GetUserIFConfig()
        {
            List<UserIFConfig> list = new List<UserIFConfig>();
            HIDB.dbo cmd = base.conn();

            try
            {
                DataTable dt = cmd.GetDataTable("select * from [Working_db].[UserIFConfig]");
                list = dt.MapToList<UserIFConfig>();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
        public List<SQLObjects> GetObjects(string Schema, string ObjectName, string ColumnName)
        {
            List<SQLObjects> list = new List<SQLObjects>();
            HIDB.dbo cmd = base.conn();

            try
            {
                SqlParameter[] para = new SqlParameter[] { 
                new SqlParameter("@Schema",Schema)
                ,new SqlParameter("@ObjectName",ObjectName)
                ,new SqlParameter("@ColumnName",ColumnName)
                };
                DataTable dt = cmd.GetDataTable("select * from GetObjects(@Schema,@ObjectName,@ColumnName)", para);
                list = dt.MapToList<SQLObjects>();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
        public DataTable RunSQL(string sql)
        {
            List<SQLObjects> list = new List<SQLObjects>();
            HIDB.dbo cmd = base.DBReadOnly();

            try
            {
                SqlParameter[] para = new SqlParameter[] { 
                new SqlParameter("@sql",sql) 
                };
                DataTable dt = cmd.GetDataTable("exec (@sql)", para);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable RunSQL(string sql, string type, string connStr)
        { 
            try
            {
                DataTable dt = new DataTable();
                switch (type)
                {
                    case "1":
                        dt = DB.DBSqlConn(connStr, sql);
                        break;
                    case "2":
                        dt = DB.DBOracleConn(connStr, sql);
                        break;
                    case "4":
                        dt = DB.DBMySqlConn(connStr, sql);
                        break;
                    case "8":
                        dt = DB.DBNpgsqlConn(connStr, sql);
                        break;
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public List<EtlSourceSystemInfo> GetSourceList()
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                DataTable dt = cmd.GetDataTable("select * from Working_db.ETL_SourceSystem order by CreateTime desc");
                return dt.MapToList<EtlSourceSystemInfo>();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public List<EtlTargetSystemInfo> GetTargetList()
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                DataTable dt = cmd.GetDataTable("select * from Working_db.ETL_TargetSystem order by CreateTime desc");
                return dt.MapToList<EtlTargetSystemInfo>();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public List<ETL_DBDriverConfig> GetDBDriverConfig()
        {
            List<ETL_DBDriverConfig> list = new List<ETL_DBDriverConfig>();
            HIDB.dbo cmd = base.conn();

            try
            {
                DataTable dt = cmd.GetDataTable("select * from [Working_db].[ETL_DBDriverConfig] order by SortNum");
                list = dt.MapToList<ETL_DBDriverConfig>();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
        public int UpdateDBDriverConfig(SqlParameter[] para)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                cmd.ExecuteSql("update [Working_db].[ETL_DBDriverConfig] set [SortNum]=@SortNum,[ConnectionType]=@ConnectionType,[DBContent]=@DBContent where DBType=@DBType", para);
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DictionaryInfo GetDictById(string guid)
        {
            var list = new List<DictionaryInfo>();

            //创建DbCommand对象
            HIDB.dbo cmd = base.conn();

            //添加输入输出参数
            IDataParameter[] sqlparams = { 
             new SqlParameter("@GUID",guid)    
            };

            //执行命令返回结果
            try
            {
                DataTable dt = cmd.GetDataTable("select *,'' as ParentName from Working_db.Dictionary where GUID=@GUID", sqlparams);
                list = CreateDictionaryInfo(dt);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_DictionaryInfo_Insert", "UP_Sys_DictionaryInfo_Insert", 0, 1, ex, "", "");
            }
            if (list.Count > 0)
            {
                return list[0];
            }
            return new DictionaryInfo();
        }
        /// <summary>
        /// 往Dictionary表内添加新记录
        /// </summary>
        /// <param name="dictionaryInfo"> </param>
        /// <returns>添加记录索引值</returns>
        public int Insert(IDataParameter[] sqlparams)
        {
            int num = -1;

            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                num = int.Parse(cmd.StoredDataTable("working_db.[UP_Dictionary_Insert]", sqlparams).Rows[0][0].ToString());
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_ETL_DbConfig_Insert", "UP_ETL_DbConfig_Insert", 0, 1, ex, "", "");
            }
            return num;
        }
        /// <summary>
        /// 往Dictionary表内添加新记录
        /// </summary>
        /// <param name="dictionaryInfo"> </param>
        /// <returns>添加记录索引值</returns>
        public int InsertMT(IDataParameter[] sqlparams)
        {
            int num = -1;

            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                num = int.Parse(cmd.StoredDataTable("working_db.[UP_DictModelType_Insert]", sqlparams).Rows[0][0].ToString());
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_ETL_DbConfig_Insert", "UP_ETL_DbConfig_Insert", 0, 1, ex, "", "");
            }
            return num;
        }
        /// <summary>
        /// 往Dictionary表内添加新记录
        /// </summary>
        /// <param name="dictionaryInfo"> </param>
        /// <returns>添加记录索引值</returns>
        public int InsertDbConfig(IDataParameter[] sqlparams)
        {
            int num = -1;

            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                cmd.StoredUpdate("working_db.[UP_ETL_DbConfig_Insert]", sqlparams);
                num = 1;
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_ETL_DbConfig_Insert", "UP_ETL_DbConfig_Insert", 0, 1, ex, "", "");
            }
            return num;
        }

        /// <summary>
        /// 更新Dictionary表记录
        /// </summary>
        public int Update(SqlParameter[] pare)
        {
            int num = -1;

            //创建cmd对象
            HIDB.dbo cmd = base.conn();



            //执行命令返回结果
            try
            {
                DataTable dt = cmd.GetDataTable(@"
if(@ParentID<>'')
	begin
		if((select COUNT(1) from [Working_db].[Dictionary] where Name=@Name and ParentID=@ParentID and Status=1 and [GUID]!=@GUID)>0)
		begin
			select 3;
		end
        else
        begin
        UPDATE [Working_db].Dictionary  SET [Name]=@name, [Description]=@Description ,  [OrderNo]= @OrderNo,  [ParentID]=@ParentID where [GUID]=@GUID
        select 1        
        end
    end
else
	begin
		if((select COUNT(1) from [Working_db].[Dictionary] where Name=@Name and [ParentID]='' and Status=1 and [GUID]!=@GUID)>0)
		begin
			select 3;
		end
        else
        begin         
        UPDATE [Working_db].Dictionary  SET [Name]=@name, [Description]=@Description where [GUID]=@GUID
        select 1
        end
    end
", pare);
                if (dt != null && dt.Rows.Count > 0)
                    num = int.Parse(dt.Rows[0][0].ToString());
                else
                    num = 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return num;
        }

        /// <summary>
        /// 更新Dictionary表记录
        /// </summary>
        public int UpdateMT(SqlParameter[] pare)
        {
            int num = -1;

            //创建cmd对象
            HIDB.dbo cmd = base.conn();

            //执行命令返回结果
            try
            {
                DataTable dt = cmd.StoredDataTable("Working_db.UP_DictModelType_Update", pare);
                if (dt != null && dt.Rows.Count > 0)
                    num = Convert.ToInt32(dt.Rows[0][0]);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_DictionaryInfo_Insert", "UP_Sys_DictionaryInfo_Insert", 0, 1, ex, "", "");
            }

            return num;
        }
        /// <summary>
        /// 根据GUID字段删除DictionaryInfo表信息
        /// </summary>
        /// <param name="guid">GUID</param>
        public int Delete(string guid)
        {
            int num = -1;

            HIDB.dbo cmd = base.conn();

            IDataParameter[] sqlparams = { new SqlParameter("@GUID", guid) };

            try
            {
                cmd.ExecuteSql("update [Working_db].[Dictionary] set status=0 where GUID=@GUID", sqlparams);
                num = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return num;
        }
        public int SaveSysConfig(string TimeOut, string TimeOutLot)
        {
            int num = -1;

            HIDB.dbo cmd = base.conn();

            IDataParameter[] sqlparams = { new SqlParameter("@TimeOut", TimeOut), new SqlParameter("@TimeOutLot", TimeOutLot) };

            try
            {
                cmd.ExecuteSql(@"update [Working_db].[SysConfig] set [KeyValue]=@TimeOut where [KeyName]='TimeOut';
update [Working_db].[SysConfig] set [KeyValue]=@TimeOutLot where [KeyName]='TimeOutLot';", sqlparams);
                num = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return num;
        }
        public int SaveSysConfig(string IFName)
        {
            int num = -1;

            HIDB.dbo cmd = base.conn();

            IDataParameter[] sqlparams = { new SqlParameter("@IFName", IFName) };

            try
            {
                cmd.ExecuteSql(@"update [Working_db].[SysConfig] set [KeyValue]=@IFName where [KeyName]='InnerfaceProgram'", sqlparams);
                num = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return num;
        }
        /// <summary>
        /// 根据GUID字段删除DictionaryInfo表信息
        /// </summary>
        /// <param name="guid">GUID</param>
        public int DeleteMT(string guid)
        {
            int num = -1;

            //创建Database对象          
            HIDB.dbo cmd = base.conn();

            //添加输入输出参数
            SqlParameter[] sqlparams = { new SqlParameter("@GUID", guid) };

            //执行命令返回结果
            try
            {
                num = int.Parse(cmd.StoredDataTable("Working_db.UP_DictModelType_Delete", sqlparams).Rows[0][0].ToString());
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_RoleInfo_Insert", "UP_Sys_RoleInfo_Insert", 0, 1, ex, "", "");
            }
            return num;
        }

        /// <summary>
        /// 根据实体获取DictionaryInfo实体List对象
        /// </summary>
        /// <param name="dictionaryInfo"> </param>
        /// <returns>DictionaryInfo实体对象</returns>
        public List<DictionaryInfo> GetList(IDataParameter[] dictionaryInfo, string sort, string order)
        {
            var list = new List<DictionaryInfo>();

            var cmd = base.conn();

            try
            {
                string sql = string.Format(@"SELECT *,'' as ParentName FROM working_db.Dictionary where Status=1 and Value='0' and
name like case when(len(@Name)>0) then '%'+@Name+'%' else name end  order by {0} {1}", sort, order);
                if (!string.IsNullOrEmpty(dictionaryInfo[1].Value.ToString()))
                    sql = string.Format(@"select * from (SELECT d.*,p.Name as ParentName FROM working_db.Dictionary d left join working_db.Dictionary p on
d.ParentID=p.GUID where d.Status=1 and p.Status=1 and d.name like case when(len(@Name)>0) then '%'+@Name+'%' else d.name end
and d.ParentID = case when(len(@ParentID)>0) then @ParentID else d.ParentID end) a order by {0} {1}", sort, order);

                DataTable dt = cmd.GetDataTable(sql, dictionaryInfo);
                list = CreateDictionaryInfo(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (list != null)
            {
                return list;
            }
            return null;
        }
        public List<DictionaryInfo> GetParentList(IDataParameter[] dictionaryInfo)
        {
            var list = new List<DictionaryInfo>();

            var cmd = base.conn();

            try
            {
                DataTable dt = cmd.GetDataTable(@"SELECT *, '' as ParentName FROM working_db.Dictionary where value='0' and Status=1", dictionaryInfo);
                list = CreateDictionaryInfo(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (list != null)
            {
                return list;
            }
            return null;
        }

        /// <summary>
        /// 根据实体获取DictionaryInfo实体List对象
        /// </summary>
        /// <param name="dictionaryInfo"> </param>
        /// <returns>DictionaryInfo实体对象</returns>
        public List<DictionaryInfo> GetMTList(IDataParameter[] dictionaryInfo)
        {
            var list = new List<DictionaryInfo>();

            //创建DbCommand对象
            var cmd = base.conn();

            //执行命令返回结果
            try
            {
                DataTable dt = cmd.StoredDataTable("Working_db.UP_DictModelType_GetList", dictionaryInfo);
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    var myDictionaryInfo = new DictionaryInfo
                    {
                        Guid = DBConvert.GetString(dt.Rows[j], "GUID"),
                        Name = DBConvert.GetString(dt.Rows[j], "Name"),
                        Value = DBConvert.GetString(dt.Rows[j], "Value"),
                        Description = DBConvert.GetString(dt.Rows[j], "Description"),
                        OrderNo = DBConvert.GetInt32(dt.Rows[j], "OrderNo"),
                        ParentID = DBConvert.GetString(dt.Rows[j], "Status")
                    };
                    list.Add(myDictionaryInfo);
                }
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_DictionaryInfo_Select", "UP_Sys_DictionaryInfo_Select", 0, 1, ex, "", "");
            }
            if (list != null)
            {
                return list;
            }
            return null;
        }
        /// <summary>
        /// 生成DictionaryInfo实体对象
        /// </summary>
        private List<DictionaryInfo> CreateDictionaryInfo(DataTable dt)
        {
            var list = new List<DictionaryInfo>();
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                var myDictionaryInfo = new DictionaryInfo
                {
                    Guid = DBConvert.GetString(dt.Rows[j], "GUID"),
                    Name = DBConvert.GetString(dt.Rows[j], "Name"),
                    Value = DBConvert.GetString(dt.Rows[j], "Value"),
                    Description = DBConvert.GetString(dt.Rows[j], "Description"),
                    ParentName = DBConvert.GetString(dt.Rows[j], "ParentName"),
                    OrderNo = DBConvert.GetInt32(dt.Rows[j], "OrderNo"),
                    ParentID = DBConvert.GetString(dt.Rows[j], "ParentID")
                };
                list.Add(myDictionaryInfo);
            }
            return list;
        }

        public List<DictionaryInfo> GetListByFeathId(string featherId)
        {
            var list = new List<DictionaryInfo>();

            //创建DbCommand对象
            var cmd = base.conn();

            //添加输入输出参数
            IDataParameter[] sqlparams = {
            new SqlParameter("@FatherGUID",featherId)
                                         };

            //执行命令返回结果
            try
            {
                DataTable dt = cmd.StoredDataTable("Working_db.UP_Dictionary_GetListByFatherID", sqlparams);
                list = CreateDictionaryInfo(dt);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_DictionaryInfo_Select", "UP_Sys_DictionaryInfo_Select", 0, 1, ex, "", "");
            }
            if (list != null)
            {
                return list;
            }
            return null;
        }

        public IList<DictionaryInfo> GetListByKeyName(string keyName)
        {
            IList<DictionaryInfo> result = new List<DictionaryInfo>();
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@KeyName", keyName) };
                DataTable dt = cmd.StoredDataTable("[Working_db].[UP_Dictionary_GetListByKeyName]", sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                {
                    DictionaryInfo _temp = null;
                    foreach (DataRow item in dt.Rows)
                    {
                        _temp = new DictionaryInfo() { Name = item["Name"].ToString(), Value = item["Value"].ToString() };
                        result.Add(_temp);
                    }
                }
            }
            catch (Exception ex)
            {

            }
            return result;
        }

        public IList<DictionaryInfo> GetMTListByKeyName(string keyName)
        {
            IList<DictionaryInfo> result = new List<DictionaryInfo>();
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@KeyName", keyName) };
                DataTable dt = cmd.GetDataTable(@"select a.Name,a.Value from [Working_db].[DictModelType] a 
	inner join(select [GUID] from [Working_db].[DictModelType] where Description=@KeyName) b
	on a.parentid = b.[guid] order by a.OrderNo", sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                {
                    DictionaryInfo _temp = null;
                    foreach (DataRow item in dt.Rows)
                    {
                        _temp = new DictionaryInfo() { Name = item["Name"].ToString(), Value = item["Value"].ToString() };
                        result.Add(_temp);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public string GetNameByKeyNameAndValue(string keyName, string value)
        {
            string result = "";
            HIDB.dbo cmd = base.conn();
            SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@KeyName", keyName), new SqlParameter("@Value", value) };
            DataTable dt = cmd.StoredDataTable("[Working_db].[UP_Dictionary_GetNameByKeyNameAndValue]", sqlparams);
            if (dt != null && dt.Rows.Count > 0)
            {
                result = dt.Rows[0][0].ToString();
            }
            return result;
        }
    }
}